package jwlee;

import java.io.BufferedReader;
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStreamReader;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;

@SuppressWarnings("unchecked")
public class SQLAutoExecutor {

	public List getFilePathList(String filePath) {
		System.out.println("getFilePathList : " + filePath);
		List filePathList = new ArrayList();
		File fileOrDir = new File(filePath);
		if (fileOrDir.isDirectory()) {
			String[] fileNames = fileOrDir.list();
			for (int i = 0; i < fileNames.length; i++) {
				String pathname = fileOrDir.getPath() + "\\" + fileNames[i];
				System.out.println("files" + i + ":" + pathname);
				if (new File(pathname).isDirectory()) {
					filePathList.addAll(this.getFilePathList(pathname));
				} else if ("sql".equalsIgnoreCase(this.getExtension(pathname))
						|| "prc".equalsIgnoreCase(this.getExtension(pathname))) {
					filePathList.add(pathname);
				}
			}
		} else if (fileOrDir.isFile()) {
			String pathname = fileOrDir.getPath();
			System.out.println("file:" + pathname);
			filePathList.add(pathname);
		}
		return filePathList;
	}

	private String getExtension(String fileName) {
		int pos = fileName.lastIndexOf(".");
		if (pos == -1)
			return "";
		return fileName.substring(pos + 1, fileName.length());
	}

	public String readPrc(String filePathAndName, String encoding) {
		System.out.println("readPrc : " + filePathAndName);
		String fileContent;
		try {
			FileInputStream fs = new FileInputStream(filePathAndName);
			InputStreamReader isr;
			if (encoding == null || "".equals(encoding.trim())) {
				isr = new InputStreamReader(fs);
			} else {
				isr = new InputStreamReader(fs, encoding);
			}
			BufferedReader br = new BufferedReader(isr);
			String perLine;
			StringBuffer str = new StringBuffer();
			while ((perLine = br.readLine()) != null) {
				if ("/".equals(perLine.trim())) {
					break;
				}
				str.append(perLine + "\n");
			}
			fileContent = str.toString().trim();
		} catch (IOException e) {
			e.printStackTrace();
			fileContent = "";
		}
		System.out.println("fileContent:\n" + fileContent);
		return fileContent;
	}

	public List readSql(String filePathAndName, String encoding) {
		System.out.println("readSql : " + filePathAndName);
		List fileContent = new ArrayList();
		try {
			FileInputStream fs = new FileInputStream(filePathAndName);
			InputStreamReader isr;
			if (encoding == null || "".equals(encoding.trim())) {
				isr = new InputStreamReader(fs);
			} else {
				isr = new InputStreamReader(fs, encoding);
			}
			BufferedReader br = new BufferedReader(isr);
			String perLine;
			StringBuffer strBuf = new StringBuffer();
			String sql;
			while ((perLine = br.readLine()) != null) {
				perLine = perLine.trim();
				if (perLine.equals("")) {
					sql = strBuf.toString();
					if (!"".equals(sql.trim())) {
						fileContent.add(sql);
					}
					strBuf = new StringBuffer();
				} else if (perLine.lastIndexOf(";") == perLine.length() - 1) {
					strBuf.append(perLine.substring(0, perLine.length() - 1));
					sql = strBuf.toString();
					fileContent.add(sql);
					strBuf = new StringBuffer();
				} else {
					strBuf.append(perLine + "\n");
				}
			}
			sql = strBuf.toString();
			if (!"".equals(sql.trim())) {
				fileContent.add(sql);
			}
		} catch (IOException e) {
			e.printStackTrace();
		}
		System.out.println("fileContent:\n" + fileContent);
		return fileContent;
	}

	public Connection getConn(String driver, String url, String user,
			String password) {
		Connection conn = null;
		try {
			Class.forName(driver);
			conn = DriverManager.getConnection(url, user, password);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return conn;
	}

	public void closeConn(Connection conn) {
		try {
			if (conn != null)
				conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	public int executeSql(Connection conn, String sql) {
		System.out.println("executeSql:\n" + sql);
		int result = 0;
		try {
			Statement stmt = conn.createStatement();
			result = stmt.executeUpdate(sql);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return result;
	}

	public void executeScripts(Connection conn, String filePath) {
		List files = this.getFilePathList(filePath);
		if (conn == null)
			return;
		Iterator it = files.iterator();
		String perFile;
		while (it.hasNext()) {
			perFile = (String) it.next();
			if ("sql".equalsIgnoreCase(this.getExtension(perFile))) {
				List sqls = this.readSql(perFile, "GBK");
				for (int i = 0; i < sqls.size(); i++) {
					this.executeSql(conn, (String) sqls.get(i));
				}
			} else if ("prc".equalsIgnoreCase(this.getExtension(perFile))) {
				String prc = this.readPrc(perFile, "GBK");
				this.executeSql(conn, prc);
			}
		}
	}

	public static void main(String args[]) {
		String a = (String) null;
		System.out.println(a == null + "," + "null".equals(a));

		String driver = "oracle.jdbc.driver.OracleDriver";
		// String url = "jdbc:oracle:thin:@MZJPC:1521:mzjdb";
		String url = "jdbc:oracle:thin:@10.13.0.252:1521:cqkf";
		// String url = "jdbc:oracle:thin:@10.13.0.248:1521:nxdb";
		String user = "db_zgxt";
		String password = "cssgdlt";
		String filePath = "D:\\宁夏社保\\SBFSB001单位社保费申报\\src";

		SQLAutoExecutor exec = new SQLAutoExecutor();
		Connection conn = exec.getConn(driver, url, user, password);
		exec.executeScripts(conn, filePath);
		// Connection conn = exec.getConn(args[0], args[1], args[2], args[3]);
		// exec.executeScripts(conn, args[4]);
		exec.closeConn(conn);
	}
	// create SQLAutoExecutor.mf:
	// Main-Class: mzj.SQLAutoExecutor
	// Class-Path: ojdbc14.jar
	// >jar cfm SQLAutoExecutor.jar SQLAutoExecutor.mf mzj\SQLAutoExecutor.class
	// >java -jar SQLAutoExecutor.jar oracle.jdbc.driver.OracleDriver
	// jdbc:oracle:thin:@10.13.0.248:1521:nxdb db_zgxt csszgxt910
	// D:\\宁夏社保\\SBFSB001单位社保费申报\\src
}
